<?php

class SqliteTriggerGenerator
{
    // include delete trigger statements before the triggers
    private $inc_delete;
    
    // include the create table statement(s) used to generate the triggers?
    private $inc_create;
    
    // debug
    private $debug;
    
    public function __construct( $inc_delete = false, $inc_create = false, $debug = false )
    {
        $this->inc_delete = $inc_delete;
        $this->inc_create = $inc_create;
        $this->debug = $debug;
    }
    
    // $statement is the sql from which the triggers should be generated
    public function create( $statement ) {

        $inc_delete = $this->inc_delete;
       
        if( !empty( $this->inc_create ) ) {
            $retval[] = $statement;
        } else {
            $retval = array();
        }
       
        $statements = explode( ';', $statement );
       
        if( !empty($data['debug']) ) {
            sa( $statements );
        }
       
        $bracket_variable = '\s+?\[?([^]\s]+)\]?\s*?';
        $column_variable = '\[?([^]\s]+)\]?\s+?';
       
        foreach( $statements as $create_statement ) {
            $create_statement = trim( $create_statement );
            if( !empty( $create_statement ) ) {
                $tbl_regex = "/create table( if not exists)?{$bracket_variable}\((.+)\)/is";
                if( preg_match( $tbl_regex, $create_statement, $matches ) != 1 ) {
                    return array( 'Did NOT recognize a CREATE TABLE statement.');
                }

                $table_name = $matches[2];
                $column_defs = explode(',', $matches[3]);
               
                if( !empty($this->debug) ) {
                    test_p( $table_name );
                    sa( $column_defs );
                }
               
                foreach( $column_defs as $cdef ) {
                    $cdef = trim( $cdef );
                    $col_regex = "/^$column_variable.+CONSTRAINT{$bracket_variable}REFERENCES\s+?([^\s]+)\s*\(([^\s)]+)\)/is";
                    if( preg_match( $col_regex, $cdef, $matches ) == 1 ) {
                        if( !empty($this->debug) ) {
                            test_p( 'Matched column:' );
                            test_p( $cdef );
                        }
                        $column = $matches[1];
                        $rtable = $matches[3];
                        $rcolumn = $matches[4];
                       
                        if( stripos( $cdef, 'NOT NULL' ) === false ) {
                            $nulls_ok = true;
                        } else {
                            $nulls_ok = false;
                        }
                       
                        $retval[] = $this->generateInsertSql( $table_name, $column, $rtable, $rcolumn, $nulls_ok, $inc_delete );
                        $retval[] = $this->generateUpdateSql( $table_name, $column, $rtable, $rcolumn, $nulls_ok, $inc_delete );
                        if( stripos( $cdef, 'on delete cascade' ) !== false ) {
                            $retval[] = $this->generateDeleteCascadeSql( $table_name, $column, $rtable, $rcolumn, $inc_delete );
                        } else {
                            $retval[] = $this->generateDeleteSql( $table_name, $column, $rtable, $rcolumn, $inc_delete );
                        }
                    }
                }
            }
        }
        return $retval;
       
    }
   
   
    private function generateInsertSql( $table, $field, $ftable, $ffield, $nulls_ok, $inc_delete ) {
        $trigger_name = "fki_{$table}_{$field}_{$ftable}_$ffield";
        if( $nulls_ok ) {
            $null_str = "NEW.$field IS NOT NULL AND ";
        } else {
            $null_str = '';
        }
        $sql = '';
        if( $inc_delete ) {
            $sql .= <<<EOT

-- Drop Trigger
DROP TRIGGER $trigger_name;

EOT;
        }
        $sql .= <<<EOT

-- Foreign Key Preventing insert
CREATE TRIGGER $trigger_name
BEFORE INSERT ON [$table]
FOR EACH ROW BEGIN
  SELECT RAISE(ROLLBACK, 'insert on table "$table" violates foreign key constraint "$trigger_name"')
  WHERE $null_str(SELECT $ffield FROM $ftable WHERE $ffield = NEW.$field) IS NULL;
END;
EOT;
        return $sql;
    }
   
    private function generateUpdateSql( $table, $field, $ftable, $ffield, $nulls_ok, $inc_delete ) {
        $trigger_name = "fku_{$table}_{$field}_{$ftable}_$ffield";
        if( $nulls_ok ) {
            $null_str = "NEW.$field IS NOT NULL AND ";
        } else {
            $null_str = '';
        }
       
        $sql = '';
        if( $inc_delete ) {
            $sql .= <<<EOT

-- Drop Trigger
DROP TRIGGER $trigger_name;

EOT;
        }
       
        $sql .= <<<EOT

-- Foreign key preventing update
CREATE TRIGGER $trigger_name
BEFORE UPDATE ON [$table]
FOR EACH ROW BEGIN
    SELECT RAISE(ROLLBACK, 'update on table "$table" violates foreign key constraint "$trigger_name"')
      WHERE $null_str(SELECT $ffield FROM $ftable WHERE $ffield = NEW.$field) IS NULL;
END;
EOT;
        return $sql;
    }
   
    private function generateDeleteCascadeSql( $table, $field, $ftable, $ffield, $inc_delete ) {
        $trigger_name = "fkdc_{$table}_{$field}_{$ftable}_$ffield";
       
        $sql = '';
        if( $inc_delete ) {
            $sql .= <<<EOT

-- Drop Trigger
DROP TRIGGER $trigger_name;

EOT;
        }
       
        $sql .= <<<EOT

-- Cascading Delete
CREATE TRIGGER $trigger_name
BEFORE DELETE ON $ftable
FOR EACH ROW BEGIN
    DELETE FROM $table WHERE $table.$field = OLD.$ffield;
END;
EOT;
        return $sql;
    }
   
    private function generateDeleteSql( $table, $field, $ftable, $ffield, $inc_delete ) {
        $trigger_name = "fkd_{$table}_{$field}_{$ftable}_$ffield";
                $sql = '';
        if( $inc_delete ) {
            $sql .= <<<EOT

-- Drop Trigger
DROP TRIGGER $trigger_name;

EOT;
        }
       
        $sql .= <<<EOT

-- Foreign key preventing delete
CREATE TRIGGER $trigger_name
BEFORE DELETE ON $ftable
FOR EACH ROW BEGIN
  SELECT RAISE(ROLLBACK, 'delete on table "$ftable" violates foreign key constraint "$trigger_name"')
  WHERE (SELECT $field FROM $table WHERE $field = OLD.$ffield) IS NOT NULL;
END;
EOT;
        return $sql;
    }

}
?>
